﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using System.Configuration;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingScholarshipDtllsDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataSet GetData (Nullable<int> scholar_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@scholar_seq", scholar_seq);
            //dbManager.AddParameters(1, "@train_seq", TrainSeq);
            dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

            string sql = "SELECT hrm_scholarships_groups.scholar_group_seq"
                        + ", hrm_scholarships_groups.scholar_group_descr"
                        + ", hrm_scholarships_groups.amount"
                        + ", hrm_scholarships_groups.scholar_seq"
                        + ", hrm_scholarships.scholar_descr"
                        + " FROM hrm_scholarships_groups"
                        + " Left Join hrm_scholarships ON hrm_scholarships_groups.scholar_seq = hrm_scholarships.scholar_seq"
                        + " WHERE ss.record_status not like @record_status"
                        + (scholar_seq != null ? " AND ss.scholar_seq = @scholar_seq" : "");
                        //+ ", CONCAT(np.prefix_short, pe.first_name, ' ', pe.last_name) AS FULL_NAME"
                        //+ ", CASE WHEN tt.score = 1 then 'ผ่าน'"
                        //+ " WHEN tt.score = 0 then 'ไม่ผ่าน'"
                        //+ " WHEN tt.score > 1 then ' ' end as T_SCORE"
                        //+ ", CASE WHEN tt.score > 1 then cast(tt.score as CHAR) else ' ' end NUM_SCORE"
                        //+ ", (select sum(quota) from hrm_training_courses_quota tq where tq.train_seq = tt.train_seq GROUP BY train_seq ) AS COUNT_QUTA"
                        

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }

        public DataSet GetDataScholarshipDtls(int scholar_fund_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@scholar_fund_seq", scholar_fund_seq);
            dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

            string sql = "SELECT s.scholar_type_seq"
                        + ", sf.scholar_fund_seq"
                        + ", st.scholar_type_descr"
                        + ", s.amount"
                        + ", s.date_sign_start"
                        + ", s.date_sign_end"
                        + ", sf.scholar_fund_descr"
                        + ", med.edu_degree_name"
                        + ", mc.country_name"
                        + " FROM hrm_scholarship_funds sf"
                        + " Left Join hrm_scholarships s ON sf.scholar_fund_seq = s.scholar_fund_seq"
                        + " Left Join hrm_scholarship_types st ON s.scholar_type_seq = st.scholar_type_seq"
                        + " Left Join mas_countries mc ON s.country_seq = mc.country_seq"
                        + " Left Join mas_edu_degrees med ON s.edu_degree_seq = med.edu_degree_seq"
                        + " WHERE sf.scholar_fund_seq = @scholar_fund_seq"
                        + " AND sf.record_status = @record_status";

            //+ ", CONCAT(np.prefix_short, pe.first_name, ' ', pe.last_name) AS FULL_NAME"
            //+ ", CASE WHEN tt.score = 1 then 'ผ่าน'"
            //+ " WHEN tt.score = 0 then 'ไม่ผ่าน'"
            //+ " WHEN tt.score > 1 then ' ' end as T_SCORE"
            //+ ", CASE WHEN tt.score > 1 then cast(tt.score as CHAR) else ' ' end NUM_SCORE"
            //+ ", (select sum(quota) from hrm_training_courses_quota tq where tq.train_seq = tt.train_seq GROUP BY train_seq ) AS COUNT_QUTA"


            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void Insert (string scholar_descr, int scholar_type_seq, int scholar_fund_seq, Nullable<int> scholar_fund_seq2 
                            , int edu_degree_seq, int country_seq, Nullable<int> epc_seq, int amount, int closed, DateTime date_sign_start
                            , DateTime date_sign_end/*, time_start, time_end*/,string edu_year_end, string edu_year_start, Nullable<int> created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@scholar_descr", scholar_descr);
                dbManager.AddParameters(1, "@scholar_type_seq", scholar_type_seq);
                dbManager.AddParameters(2, "@scholar_fund_seq", scholar_fund_seq); 
                dbManager.AddParameters(3, "@scholar_fund_seq2", scholar_fund_seq2);
                dbManager.AddParameters(4, "@edu_degree_seq", edu_degree_seq);
                dbManager.AddParameters(5, "@country_seq", country_seq);
                dbManager.AddParameters(6, "@epc_seq", epc_seq);
                dbManager.AddParameters(7, "@amount", amount);
                dbManager.AddParameters(8, "@closed", closed); 
                dbManager.AddParameters(9, "@date_sign_start", date_sign_start);
                dbManager.AddParameters(10, "@date_sign_end", date_sign_end);                
                dbManager.AddParameters(11, "@edu_year_start", edu_year_start); 
                dbManager.AddParameters(12, "@edu_year_end", edu_year_end);  
                dbManager.AddParameters(13, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@created_by", created_by);
                dbManager.AddParameters(15, "@created_when", DateTime.Now);
                //dbManager.AddParameters(16, "@time_start", time_start);
                //dbManager.AddParameters(17, "@time_end", time_end);

                strSQL = "INSERT hrm_scholarships"
                    + "(scholar_descr"
                    + ", scholar_type_seq"
                    + ", scholar_fund_seq"
                    + ", scholar_fund_seq2"
                    + ", edu_degree_seq"
                    + ", country_seq"
                    + ", epc_seq"
                    + ", amount"
                    + ", closed"
                    + ", date_sign_start"
                    + ", date_sign_end"
                   // + ", time_start"
                    //+ ", time_end"
                    + ", edu_year_end"
                    + ", edu_year_start"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@scholar_descr"
                    + ", @scholar_type_seq"
                    + ", @scholar_fund_seq"
                    + ", @scholar_fund_seq2"
                    + ", @edu_degree_seq"
                    + ", @country_seq"
                    + ", @epc_seq"
                    + ", @amount"
                    + ", @closed"
                    + ", @date_sign_start"
                    + ", @date_sign_end"
                    //+ ", @time_start"
                   // + ", @time_end"
                    + ", @edu_year_end"
                    + ", @edu_year_start"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertSchGroup(string scholar_group_descr, int amount, int scholar_seq, Nullable<int> created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@scholar_group_descr", scholar_group_descr);
                dbManager.AddParameters(1, "@amount", amount);
                dbManager.AddParameters(2, "@scholar_seq", scholar_seq);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);


                strSQL = "INSERT hrm_scholarships_groups"
                    + "(scholar_group_descr"
                    + ", amount"
                    + ", scholar_seq"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@scholar_group_descr"
                    + ", @amount"
                    + ", @scholar_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataSet GetDataSchGroup(Nullable<int> scholar_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@scholar_seq", scholar_seq);
            dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);

            string sql = "SELECT hrm_scholarships_groups.scholar_group_seq"
                        + ", hrm_scholarships_groups.scholar_group_descr"
                        + ", hrm_scholarships_groups.amount"
                        + " FROM hrm_scholarships_groups"
                        + " WHERE hrm_scholarships_groups.record_status = @record_status"
                        + (scholar_seq > 0 ? " AND hrm_scholarships_groups.scholar_seq = @scholar_seq " : "");


            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }


        public void DeleteSchGroup(int scholar_group_seq, int updated_by )
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@scholar_group_seq", scholar_group_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE  hrm_scholarships_groups"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE scholar_group_seq = @scholar_group_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
